package com.lsmgc.cms.dao.article;

import com.lsmgc.cms.entity.article.Article;
import com.lsmgc.cms.entity.article.ArticleQuery;
import com.lsmgc.cms.util.jdbc.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.StringUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ArticleDao {
    public int addArticle(Article article) throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "insert into article(title,content,img,add_time) value(?,?,?,?)";
        return runner.update(sql, article.getTitle(), article.getContent(), article.getImg(), article.getAddTime());
    }

    public List<Article> getArticleListPage(ArticleQuery articleQuery) throws SQLException {
        List<Object> param = new ArrayList<>();
        QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
        StringBuilder sql = new StringBuilder();
        sql.append("select * from article");
        if (StringUtils.isNotBlank(articleQuery.getTitle())) {
            sql.append(" where");
            sql.append(" title like ?");
            param.add("%" + articleQuery.getTitle() + "%");
        }
        sql.append(" order by id desc");
        sql.append(" limit ?,?");
        param.add(articleQuery.getStartRow());
        param.add(articleQuery.getPageSize());
        System.out.println("sql: " + sql.toString());
        return qr.query(sql.toString(), new BeanListHandler<>(Article.class), param.toArray());
    }

    public Long getArticleListPageCount(ArticleQuery articleQuery) throws SQLException {
        List<Object> param = new ArrayList<>();
        QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
        StringBuilder sql = new StringBuilder();
        sql.append("select count(1) num from article");
        if (StringUtils.isNotBlank(articleQuery.getTitle())) {
            sql.append(" where");
            sql.append(" title like ?");
            param.add("%" + articleQuery.getTitle() + "%");
        }
        System.out.println("sql: " + sql.toString());
        return qr.query(sql.toString(), new ScalarHandler<>("num"), param.toArray());
    }

    public List<Article> getArticleList(ArticleQuery articleQuery) throws SQLException {
        QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from article order by id limit ?,?";
        return qr.query(sql, new BeanListHandler<>(Article.class));
    }

    public Article getArticleById(Long id) throws SQLException {
        QueryRunner qr = new QueryRunner((C3P0Utils.getDataSource()));
        String sql = "select * from article where id=?";
        return qr.query(sql, new BeanHandler<>(Article.class), id);
    }

    public int updateArticleById(Article article) throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "update article set title=?,content=?,img=? where id=?";
        return runner.update(sql, article.getTitle(), article.getContent(), article.getImg(), article.getId());
    }

    public int deleteArticleById(Long id) throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "delete from article where id=?";
        return runner.update(sql, id);
    }
}
